AFTER INSERT Trigger

This Oracle tutorial explains how to create an AFTER INSERT Trigger in Oracle with syntax and examples.

Description

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

Syntax

The syntax to create an AFTER INSERT Trigger in Oracle/PLSQL is:

CREATE [ OR REPLACE ] TRIGGER trigger_name

AFTER INSERT

   ON table_name

   [ FOR EACH ROW ]

 

DECLARE

   -- variable declarations

 

BEGIN

   -- trigger code

 

EXCEPTION

   WHEN ...

   -- exception handling

 

END;

Parameters or Arguments

OR REPLACE

Optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name

The name of the trigger to create.

AFTER INSERT

It indicates that the trigger will fire after the INSERT operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create an AFTER trigger on a view.
• You can not update the :NEW values.
• You can not update the :OLD values.

Note

• See also how to create AFTER DELETE, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an AFTER INSERT trigger using the CREATE TRIGGER statement.

If you had a table created as follows:

CREATE TABLE orders

( order_id number(5),

  quantity number(4),

  cost_per_item number(6,2),

  total_cost number(8,2)

);

We could then use the CREATE TRIGGER statement to create an AFTER INSERT trigger as follows:

TIP: When using SQLPlus, you need to enter slash on a new line after the trigger. Otherwise, the script won't execute.

CREATE OR REPLACE TRIGGER orders_after_insert

AFTER INSERT

   ON orders

   FOR EACH ROW

 

DECLARE

   v_username varchar2(10);

 

BEGIN

 

   -- Find username of person performing the INSERT into the table

   SELECT user INTO v_username

   FROM dual;

 

   -- Insert record into audit table

   INSERT INTO orders_audit

   ( order_id,

     quantity,

     cost_per_item,

     total_cost,

     username )

   VALUES

   ( :new.order_id,

     :new.quantity,

     :new.cost_per_item,

     :new.total_cost,

     v_username );

 

END;

 

/